package com.cnsugar.common.jdbc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.cnsugar.common.jdbc.dialect.Dialect;
import com.cnsugar.common.jdbc.dialect.DialectUtils;
import com.cnsugar.common.jdbc.model.SaveOrUpdateModel;
import com.cnsugar.common.jdbc.utils.ArrayUtils;
import com.cnsugar.common.paging.Page;
import com.cnsugar.common.paging.cache.Cache;
import com.cnsugar.common.paging.cache.CacheFactory;
import com.cnsugar.common.reflect.BeanField;
import com.cnsugar.common.reflect.ReflectUtils;
import com.cnsugar.common.jdbc.dialect.db.OracleDialect;
import com.cnsugar.common.jdbc.model.FieldType;
import com.cnsugar.common.jdbc.utils.JdbcUtils;
import com.cnsugar.common.utils.CamelCaseUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * jdbc操作基础支持类
 * @Author Sugar 
 * @Version 2018/12/7 14:00
 */
public class JdbcDaoSupport implements Serializable {
    protected static Logger log = LoggerFactory.getLogger(JdbcDaoSupport.class);

    //分页缓存
    private static Cache<String, Integer> pageCache = null;

    protected static Cache<String, Integer> getPageCache() {
        if (pageCache != null) {
            return pageCache;
        }
        Properties properties = new Properties();
        InputStream is = null;
        String location = "classpath:page.properties";
        try {
            Resource resource = new DefaultResourceLoader().getResource(location);
            is = resource.getInputStream();
            properties.load(is);
        } catch (IOException ex) {
            log.warn("Could not load properties from path:" + location);
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException ioe) {
                // ignore
            }
        }
        pageCache = CacheFactory.createCache(properties.getProperty("pageCacheClass"), "page.", properties);
        return pageCache;
    }

    /**
     * 执行无返回值的存储过程
     *
     * @param callString 存储过程语句
     */
    public static void execute(String callString, JdbcTemplate jt) {
        if (log.isDebugEnabled()) {
            log.debug("execute: sql=" + callString);
        }
        jt.execute(callString);
    }

    /**
     * 执行无返回值的存储过程
     *
     * @param callString 存储过程语句
     * @param args       参数
     */
    public static void execute(final String callString, final Object[] args, JdbcTemplate jt) {
        if (args == null || args.length == 0) {
            execute(callString, jt);
            return;
        }
        if (log.isDebugEnabled()) {
            log.debug("execute: sql=" + callString + "; parameters=" + arrayToString(args));
        }
        jt.execute(connection -> {
            CallableStatement cs = connection.prepareCall(callString);
            int len = args.length;
            for (int i = 1; i <= len; i++) {
                cs.setObject(i, args[i - 1]);// 设置输入参数的值
            }
            return cs;
        }, (CallableStatementCallback<String>) callableStatement -> {
            callableStatement.execute();
            return null;
        });
    }

    /**
     * 执行存储过程，返回String类型
     *
     * @param callString 存储过程语句
     */
    public static String executeForString(String callString, JdbcTemplate jt) {
        return executeForString(callString, null, jt);
    }

    /**
     * 执行存储过程，返回String类型
     *
     * @param callString 存储过程语句
     * @param args       参数
     */
    public static String executeForString(final String callString, final Object[] args, JdbcTemplate jt) {
        if (log.isDebugEnabled()) {
            log.debug("executeForString: sql=" + callString + "; parameters=" + arrayToString(args));
        }
        final int len = (args == null ? 0 : args.length);
        return jt.execute(connection -> {
            CallableStatement cs = connection.prepareCall(callString);
            for (int i = 1; i <= len; i++) {
                cs.setObject(i, args[i - 1]);// 设置输入参数的值
            }
            cs.registerOutParameter(len + 1, Types.VARCHAR);// 注册输出参数的类型
            return cs;
        }, (CallableStatementCallback<String>) callableStatement -> {
            callableStatement.execute();
            return callableStatement.getString(len + 1);// 获取输出参数的值
        });
    }

    /**
     * 执行存储过程，返回int类型
     *
     * @param callString 存储过程语句
     */
    public static int executeForInt(String callString, JdbcTemplate jt) {
        return executeForInt(callString, null, jt);
    }

    /**
     * 执行存储过程，返回int类型
     *
     * @param callString 存储过程语句
     * @param args       参数
     */
    public static int executeForInt(final String callString, final Object[] args, JdbcTemplate jt) {
        if (log.isDebugEnabled()) {
            log.debug("executeForInt: sql=" + callString + "; parameters=" + arrayToString(args));
        }
        final int len = (args == null ? 0 : args.length);
        return jt.execute((CallableStatementCreator) (connection -> {
            String storedProc = callString;// 调用的sql
            CallableStatement cs = connection.prepareCall(storedProc);
            for (int i = 1; i <= len; i++) {
                cs.setObject(i, args[i - 1]);// 设置输入参数的值
            }
            cs.registerOutParameter(len + 1, Types.BIGINT);// 注册输出参数的类型
            return cs;
        }), callableStatement -> {
            callableStatement.execute();
            return callableStatement.getInt(len + 1);// 获取输出参数的值
        });
    }

    /**
     * 执行存储过程，返回List类型
     *
     * @param callString 存储过程语句
     */
    public static List<Map<String, Object>> executeForList(String callString, JdbcTemplate jt) {
        return executeForList(callString, null, jt);
    }

    /**
     * 执行存储过程，返回List类型
     *
     * @param callString 存储过程语句
     * @param args       参数
     */
    public static List<Map<String, Object>> executeForList(final String callString, final Object[] args, JdbcTemplate jt) {
        if (log.isDebugEnabled()) {
            log.debug("executeForList: sql=" + callString + "; parameters=" + arrayToString(args));
        }
        final int len = (args == null ? 0 : args.length);
        return jt.execute((CallableStatementCreator) (connection -> {
            String storedProc = callString;// 调用的sql
            CallableStatement cs = connection.prepareCall(storedProc);
            for (int i = 1; i <= len; i++) {
                cs.setObject(i, args[i - 1]);// 设置输入参数的值
            }
            cs.registerOutParameter(len + 1, Types.REF);// 注册输出参数的类型
            return cs;
        }), callableStatement -> {
            callableStatement.execute();
            ResultSet rs = callableStatement.getObject(len + 1, ResultSet.class);// 获取游标一行的值
            if (!rs.next()) {
                return null;
            }
            List<Map<String, Object>> list = new ArrayList<>();
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            // columns[0]-字段名, columns[1]-字段类型
            List<Object[]> columns = new ArrayList<>();
            for (int i = 1; i <= colCount; i++) {
                columns.add(new Object[]{meta.getColumnName(i), meta.getColumnType(i)});
            }
            meta = null;
            do {// 转换每行的返回值到Map中
                list.add(convert2Map(rs, columns));
            } while (rs.next());
            rs.close();
            return list;
        });
    }

    /**
     * 执行存储过程，返回Map类型
     *
     * @param callString 存储过程语句
     */
    public static Map<String, Object> executeForMap(String callString, JdbcTemplate jt) {
        return executeForMap(callString, null, jt);
    }

    /**
     * 执行存储过程，返回Map类型
     *
     * @param callString 存储过程语句
     * @param args       参数
     */
    public static Map<String, Object> executeForMap(final String callString, final Object[] args, JdbcTemplate jt) {
        if (log.isDebugEnabled()) {
            log.debug("executeForMap: sql=" + callString + "; parameters=" + arrayToString(args));
        }
        final int len = (args == null ? 0 : args.length);
        return jt.execute((CallableStatementCreator) (connection -> {
            String storedProc = callString;// 调用的sql
            CallableStatement cs = connection.prepareCall(storedProc);
            for (int i = 1; i <= len; i++) {
                cs.setObject(i, args[i]);// 设置输入参数的值
            }
            cs.registerOutParameter(len + 1, Types.REF);// 注册输出参数的类型
            return cs;
        }), callableStatement -> {
            callableStatement.execute();
            ResultSet rs = callableStatement.getObject(len + 1, ResultSet.class);// 获取游标一行的值
            if (!rs.next()) {
                return null;
            }
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            // columns[0]-字段名, columns[1]-字段类型
            List<Object[]> columns = new ArrayList<>();
            for (int i = 1; i <= colCount; i++) {
                columns.add(new Object[]{meta.getColumnName(i), meta.getColumnType(i)});
            }
            meta = null;
            Map<String, Object> m = convert2Map(rs, columns);
            rs.close();
            return m;
        });
    }

    /**
     * 增加、删除、修改表时，调用此方法
     *
     * @param wsql DDL语句
     * @param args 参数
     * @param jt   JdbcTemplate
     */
    public static int update(String wsql, Object[] args, int[] types, JdbcTemplate jt) {
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "update: sql=" + wsql + "; parameters=" + arrayToString(args);
            log.debug(logstr);
        }
        int rows = 0;
        if (types == null) {
            rows = jt.update(wsql, args);
        } else {
            rows = jt.update(wsql, args, types);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "update: sql=" + wsql + "; parameters=" + arrayToString(args);
            }
            log.info(logstr + "; rows=" + rows);
        }
        return rows;
    }

    /**
     * 返回int类型
     *
     * @param wsql
     * @param values
     * @param types
     * @param jt
     * @return
     */
    public static int queryForInt(String wsql, Object[] values, int[] types, JdbcTemplate jt) {
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForInt: sql=" + wsql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        Integer result = 0;
        try {
            if (values == null || values.length == 0) {
                result = jt.queryForObject(wsql, Integer.class);
            } else if (types == null || types.length == 0) {
                result = jt.queryForObject(wsql, values, Integer.class);
            } else {
                result = jt.queryForObject(wsql, values, types, Integer.class);
            }
        } catch (EmptyResultDataAccessException e) {
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForInt: sql=" + wsql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; result=" + result);
        }
        return (result == null ? 0 : result.intValue());
    }

    /**
     * 返回long类型
     *
     * @param wsql
     * @param values
     * @param types
     * @param jt
     * @return
     */
    public static long queryForLong(String wsql, Object[] values, int[] types, JdbcTemplate jt) {
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForLong: sql=" + wsql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        Long result = 0l;
        try {
            if (values == null || values.length == 0) {
                result = jt.queryForObject(wsql, Long.class);
            } else if (types == null || types.length == 0) {
                result = jt.queryForObject(wsql, values, Long.class);
            } else {
                result = jt.queryForObject(wsql, values, types, Long.class);
            }
        } catch (EmptyResultDataAccessException e) {
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForLong: sql=" + wsql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; result=" + result);
        }
        return (result == null ? 0 : result.longValue());
    }

    /**
     * 返回String类型
     *
     * @param wsql
     * @param values
     * @param types
     * @param jt
     * @return
     */
    public static String queryForString(String wsql, Object[] values, int[] types, JdbcTemplate jt) {
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForString: sql=" + wsql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        String rows = null;
        try {
            if (values == null || values.length == 0) {
                rows = jt.queryForObject(wsql, String.class);
            } else if (types == null || types.length == 0) {
                rows = jt.queryForObject(wsql, values, String.class);
            } else {
                rows = jt.queryForObject(wsql, values, types, String.class);
            }
        } catch (EmptyResultDataAccessException e) {
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForString: sql=" + wsql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; result=" + rows);
        }
        return rows;
    }

    /**
     * * 通过SQL查询一个对象的所有信息，只返回一条记录
     *
     * @param clazz  Object 需要查询的对象，这个对象是对数据库映射的一个javabean
     * @param wsql   String 查询的语句
     * @param values
     * @param types
     * @param jt
     * @return
     */
    public static <T> T queryForObject(final Class<T> clazz, String wsql, Object[] values, int[] types, JdbcTemplate jt) {
        ResultSetExtractor<T> rse = rs -> {
            if (rs.next()) {
                return convert2Object(rs, clazz);
            }
            return null;
        };
        if (wsql == null || (wsql = wsql.trim()).isEmpty()) {
            wsql = "SELECT * FROM " + JdbcUtils.getTableName(clazz);
            Dialect dialect = DialectUtils.getDialect(jt);
            if (OracleDialect.NAME.equalsIgnoreCase(dialect.getDatabseProductName())) {
                wsql += " WHERE ROWNUM<=1";
            } else {
                wsql += " LIMIT 1";
            }
        } else {
            if (!(wsql.startsWith("SELECT ") || wsql.startsWith("select "))) {
                wsql = "SELECT * FROM " + JdbcUtils.getTableName(clazz) + " WHERE " + wsql;
            }
        }

        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForObject: sql=" + wsql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        T t = null;
        if (values == null || values.length == 0) {
            t = jt.query(wsql, rse);
        } else if (types == null || types.length == 0) {
            t = jt.query(wsql, values, rse);
        } else {
            t = jt.query(wsql, values, types, rse);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForObject: sql=" + wsql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; result=" + t);
        }
        return t;
    }

    /**
     * 返回Map<String,String>对象
     *
     * @param wsql
     * @param values
     * @param types
     * @param jt
     * @return
     */
    public static Map<String, Object> queryForMap(String wsql, Object[] values, int[] types, JdbcTemplate jt) {
        ResultSetExtractor<Map<String, Object>> rse = rs -> {
            if (!rs.next()) {
                return null;
            }
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            // columns[0]-字段名, columns[1]-字段类型
            List<Object[]> columns = new ArrayList<>();
            for (int i = 1; i <= colCount; i++) {
                columns.add(new Object[]{meta.getColumnName(i), meta.getColumnType(i)});
            }
            return convert2Map(rs, columns);
        };

        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForMap: sql=" + wsql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        Map<String, Object> map = null;
        if (values == null || values.length == 0) {
            map = jt.query(wsql, rse);
        } else if (types == null || types.length == 0) {
            map = jt.query(wsql, values, rse);
        } else {
            map = jt.query(wsql, values, types, rse);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForMap: sql=" + wsql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; result=" + map);
        }
        return map;
    }

    /**
     * 从指定表中查询某个对象的列表，返回多条记录
     *
     * @param clazz   <T>
     * @param wsql
     * @param values
     * @param orderBy
     * @param jt
     * @return
     */
    public static <T> List<T> queryForList(final Class<T> clazz, String wsql, Object[] values, String orderBy, JdbcTemplate jt) {
        ResultSetExtractor<List<T>> rse = new ResultSetExtractorImpl(clazz);
        if (wsql == null || (wsql = wsql.trim()).isEmpty()) {
            wsql = "SELECT * FROM " + JdbcUtils.getTableName(clazz) + "";
        } else if (!(wsql.startsWith("SELECT ") || wsql.startsWith("select "))) {
            wsql = "SELECT * FROM " + JdbcUtils.getTableName(clazz) + " WHERE " + wsql;
        }
        if (orderBy != null && !orderBy.isEmpty()) {
            wsql += " ORDER BY " + orderBy;
        }

        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForList: sql=" + wsql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        List<T> list = null;
        if (values == null || values.length == 0) {
            list = jt.query(wsql, rse);
        } else {
            list = jt.query(wsql, values, rse);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForList: sql=" + wsql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; size=" + (list == null ? 0 : list.size()));
        }
        return list;
    }

    /**
     * 返回一个List<Map>
     *
     * @param sql
     * @param values
     * @param types
     * @param jt
     * @return
     */
    public static List<Map<String, Object>> queryForMapList(String sql, Object[] values, int[] types, JdbcTemplate jt) {
        final List<Map<String, Object>> list = new ArrayList<>();
        final List<Object[]> columns = new ArrayList<>();
        RowCallbackHandler hander = rs -> {
            if (columns.isEmpty()) {
                ResultSetMetaData meta = rs.getMetaData();
                int colCount = meta.getColumnCount();
                // columns[0]-字段名, columns[1]-字段类型
                for (int i = 1; i <= colCount; i++) {
                    columns.add(new Object[]{meta.getColumnName(i), meta.getColumnType(i)});
                }
            }
            list.add(convert2Map(rs, columns));
        };
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForList<Map>: sql=" + sql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        if (values == null || values.length == 0) {
            jt.query(sql, hander);
        } else if (types == null || types.length == 0) {
            jt.query(sql, values, hander);
        } else {
            jt.query(sql, values, types, hander);
        }

        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForList<Map>: sql=" + sql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; size=" + (list == null ? 0 : list.size()));
        }
        return list;
    }

    /**
     * 返回一个List<Integer>
     *
     * @param sql
     * @param values
     * @param jt     JdbcTemplate
     * @param unique 返回结果是否去重 ture是
     * @return List<Integer>
     */
    public static List<Integer> queryForIntegerList(String sql, Object[] values, final boolean unique, JdbcTemplate jt) {
        final List<Integer> list = new ArrayList<>();
        RowCallbackHandler hander = rs -> {
            ResultSetMetaData meta = rs.getMetaData();
            if (meta != null) {
                int colCount = meta.getColumnCount();
                if (colCount >= 1) {
                    int i = rs.getInt(1);
                    if (unique && list.contains(i)) {
                        return;
                    }
                    list.add(i);
                }
            }
        };
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForList<Integer>: sql=" + sql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        if (values == null || values.length == 0) {
            jt.query(sql, hander);
        } else {
            jt.query(sql, values, hander);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForList<Integer>: sql=" + sql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; size=" + (list == null ? 0 : list.size()));
        }
        return list;
    }

    /**
     * 返回一个List<Long>
     *
     * @param sql
     * @param values
     * @param jt     JdbcTemplate
     * @param unique 返回结果是否去重 ture是
     * @return List<Long>
     */
    public static List<Long> queryForLongList(String sql, Object[] values, final boolean unique, JdbcTemplate jt) {
        final List<Long> list = new ArrayList<>();
        RowCallbackHandler hander = rs -> {
            ResultSetMetaData meta = rs.getMetaData();
            if (meta != null) {
                int colCount = meta.getColumnCount();
                if (colCount >= 1) {
                    long i = rs.getLong(1);
                    if (unique && list.contains(i)) {
                        return;
                    }
                    list.add(i);
                }
            }
            meta = null;
        };
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForList<Long>: sql=" + sql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        if (values == null || values.length == 0) {
            jt.query(sql, hander);
        } else {
            jt.query(sql, values, hander);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForList<Long>: sql=" + sql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; size=" + (list == null ? 0 : list.size()));
        }
        return list;
    }

    /**
     * 返回一个List<String>
     *
     * @param sql
     * @param values
     * @param jt     JdbcTemplate
     * @param unique 返回结果是否去重 ture是
     * @return List<String>
     */
    public static List<String> queryForStringList(String sql, Object[] values, final boolean unique, JdbcTemplate jt) {
        final List<String> list = new ArrayList<>();
        RowCallbackHandler hander = rs -> {
            ResultSetMetaData meta = rs.getMetaData();
            if (meta != null) {
                int colCount = meta.getColumnCount();
                if (colCount >= 1) {
                    String i = rs.getString(1);
                    if (unique && list.contains(i)) {
                        return;
                    }
                    list.add(i);
                }
            }
            meta = null;
        };
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "queryForList<String>: sql=" + sql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        if (values == null || values.length == 0) {
            jt.query(sql, hander);
        } else {
            jt.query(sql, values, hander);
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "queryForList<String>: sql=" + sql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; size=" + (list == null ? 0 : list.size()));
        }
        return list;
    }

    /**
     * 查询分页对象
     *
     * @param page
     * @param wsql
     * @param values
     * @param jt
     * @return
     */
    public static <T> Page queryForPage(Page page, String wsql, Object[] values, JdbcTemplate jt) {
        if (page.getPageSize() <= 0) {
            return page;
        }
        if (wsql == null || (wsql = wsql.trim()).isEmpty()) {
            wsql = "SELECT a.* FROM " + JdbcUtils.getTableName(page.getClazz()) + " a";
        } else if (!(wsql.startsWith("SELECT ") || wsql.startsWith("select "))) {
            wsql = "SELECT a.* FROM " + JdbcUtils.getTableName(page.getClazz()) + " a WHERE " + wsql;
        }

        if (page.getPageNo() > 1) {// 不是第一页
            Integer total = getPageCache().get(wsql);
            page.setTotalCount(total == null ? 0 : total.intValue());
        }
        if (page.getTotalCount() <= 0) {
            int total = getTotalCount(wsql, values, jt);
            page.setTotalCount(total);
            if (total <= 0) {
                return page;
            }
            getPageCache().put(wsql, total);
        }

        Dialect dialect = DialectUtils.getDialect(jt);
        wsql = dialect.getLimitString(wsql, (page.getPageNo() - 1) * page.getPageSize(), page.getPageSize());
        page.setList(queryForList(page.getClazz(), wsql, values, null, jt));
        return page;
    }

    /**
     * 查询分页对象，结果集中存Map对象
     *
     * @param page
     * @param wsql
     * @param values
     * @param jt
     * @return
     */
    public static Page queryForPageWithMap(Page page, String wsql, Object[] values, JdbcTemplate jt) {
        if (page.getPageSize() <= 0) {
            return page;
        }
        if (page.getPageNo() > 1) {// 不是第一页
            Integer total = getPageCache().get(wsql);
            page.setTotalCount(total == null ? 0 : total.intValue());
        }
        if (page.getTotalCount() <= 0) {
            int total = getTotalCount(wsql, values, jt);
            page.setTotalCount(total);
            if (total <= 0) {
                return page;
            }
            getPageCache().put(wsql, total);
        }
        if (page.getTotalCount() == 0) {
            int total = getTotalCount(wsql, values, jt);
            page.setTotalCount(total);
        }
        Dialect dialect = DialectUtils.getDialect(jt);
        wsql = dialect.getLimitString(wsql, (page.getPageNo() - 1) * page.getPageSize(), page.getPageSize());
        page.setList(queryForMapList(wsql, values, null, jt));
        return page;
    }

    /**
     * 获取总记录条数
     *
     * @param sql
     * @return
     */
    protected static int getTotalCount(String sql, Object[] values, JdbcTemplate jt) {
        // 将关键词转成大写
        sql = sql.replace(" from ", " FROM ").replace(" where ", " WHERE ").replace(" order by ", " ORDER BY ").replace(" distinct ", " DISTINCT ")
                .replace(" group by ", " GROUP BY ");
        StringBuilder s = new StringBuilder();
        // 如果sql比较简单，用子查询会降低count()性能，所以先判断下sql是否复杂
        if (sql.split(" FROM ").length > 2 || sql.contains(" DISTINCT ") || sql.contains(" GROUP BY ")) {
            s.append("SELECT COUNT(1) FROM (").append(sql).append(") TMP");
        } else {
            String from = JdbcUtils.parseHql(sql, 1);
            if (log.isDebugEnabled()) {
                log.debug("parse sql result - from:" + from);
            }
            if (from == null) {
                log.error(">sql 无效：" + sql);
                return 0;
            }
            String where = JdbcUtils.parseHql(sql, 2);
            if (log.isDebugEnabled()) {
                log.debug("parse sql result - where:" + where);
            }
            if (where == null) {
                where = "";
            }
            int f = from.indexOf(" FROM ");
            String fromTrim = from.substring(f);
            s.append("SELECT COUNT(1) ").append(fromTrim).append(" ").append(where);
        }
        if (log.isDebugEnabled()) {
            log.debug("Get total count sql：" + s);
        }
        if (values == null || values.length == 0) {
            return queryForInt(s.toString(), null, null, jt);
        } else {
            return queryForInt(s.toString(), values, null, jt);
        }
    }

    /**
     * 插入或更新
     *
     * @param sql
     * @param values
     * @param types
     * @param pkName
     * @param jt
     * @param <T>
     * @return
     */
    protected static <T> long save(final String sql, final Object[] values, final int[] types, final String pkName, final JdbcTemplate jt) {
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "save: sql=" + sql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        if (pkName == null || pkName.isEmpty()) {
            int row = jt.update(new PreparedStatementCreatorImpl(sql, values, types));
            if (log.isInfoEnabled()) {
                if (logstr == null) {
                    logstr = "save: sql=" + sql + "; parameters=" + arrayToString(values);
                }
                log.info(logstr + "; row=" + row);
            }
            return row;
        } else {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jt.update(new PreparedStatementCreatorImpl(sql, values, types, pkName), keyHolder);
            Number n = keyHolder.getKey();
            if (n == null) {
                return 0;
            }
            long id = n.longValue();
            if (log.isInfoEnabled()) {
                if (logstr == null) {
                    logstr = "save: sql=" + sql + "; parameters=" + arrayToString(values);
                }
                log.info(logstr + "; pk=" + id);
            }
            return id;
        }
    }

    /**
     * 插入或更新
     *
     * @param sql
     * @param values
     * @param pkName
     * @param jt
     * @return
     */
    public static long save(String sql, Object[] values, String pkName, JdbcTemplate jt) {
        return save(sql, values, null, pkName, jt);
    }

    /**
     * 保存并返回影响行数
     *
     * @param sql
     * @param values
     * @param types
     * @param jt
     * @return
     */
    protected static <T> boolean insert(final String sql, final Object[] values, final int[] types, final JdbcTemplate jt) {
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "insert: sql=" + sql + "; parameters=" + arrayToString(values);
            log.debug(logstr);
        }
        int row = 0;
        if (types == null) {
            row = jt.update(sql, values);
        } else {
            row = jt.update(new PreparedStatementCreatorImpl(sql, values, types));
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "insert: sql=" + sql + "; parameters=" + arrayToString(values);
            }
            log.info(logstr + "; rows=" + row);
        }

        return row > 0;
    }

    /**
     * 新增或更新对象
     *
     * @param t
     * @param jt
     * @return
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> boolean saveOrUpdate(T t, JdbcTemplate jt) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException,
            NoSuchMethodException,
            SecurityException {
        Dialect dialect = DialectUtils.getDialect(jt);
        SaveOrUpdateModel model = JdbcUtils.buildSaveOrUpdateModel(t);
        boolean isNew = true;
        if (model.pkValue != null && !"".equals(model.pkValue + "") && !"0".equals(model.pkValue + "")) {
            String s = "SELECT COUNT(1) FROM " + model.table + " WHERE " + dialect.getMark() + model.pkName + dialect.getMark() + "=?";
            if (jt.queryForObject(s, new Object[]{model.pkValue}, String.class) != null) {
                isNew = false;
            }
        }
        if (!isNew) {
            StringBuilder sql_set = new StringBuilder();
            int size = model.columns.size();
            int[] types = new int[size];
            Object[] values = new Object[size];
            int j = 0;
            Object val;
            String column;
            for (int i = 0; i < size; i++) {
                column = model.columns.get(i);
                if (column.equalsIgnoreCase(model.pkName)) {
                    values[size - 1] = model.values.get(i);
                    types[size - 1] = model.types.get(i).intValue();
                    continue;
                }
                val = model.values.get(i);
                if (String.valueOf(val).toUpperCase().endsWith(".NEXTVAL")) {
                    sql_set.append(",").append(dialect.getMark()).append(column).append(dialect.getMark()).append("=").append(val);
                } else {
                    sql_set.append(",").append(dialect.getMark()).append(column).append(dialect.getMark()).append("=?");
                    values[j] = val;
                    types[j] = model.types.get(i).intValue();
                    j++;
                }
            }
            if (j != size - 1) {
                values[j + 1] = values[size - 1];
                types[j + 1] = types[size - 1];
                values = ArrayUtils.subArray(values, j + 1);
                types = ArrayUtils.subArray(types, j + 1);
            }
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(" ").append(model.table);
            sql.append(" SET ").append(sql_set.substring(1));
            sql.append(" WHERE ").append(dialect.getMark()).append(model.pkName).append(dialect.getMark()).append("=?");
            return update(sql.toString(), values, types, jt) >= 0;
        } else {
            StringBuilder sql_fields = new StringBuilder();
            StringBuilder sql_values = new StringBuilder();
            int size = model.columns.size();
            int[] types = new int[size];
            Object[] values = new Object[size];
            int j = 0;
            Object val;
            for (int i = 0; i < size; i++) {
                sql_fields.append(",").append(dialect.getMark()).append(model.columns.get(i)).append(dialect.getMark());
                val = model.values.get(i);
                if (String.valueOf(val).endsWith(".NEXTVAL")) {
                    sql_values.append(",").append(val);
                } else {
                    sql_values.append(",?");
                    values[j] = val;
                    types[j] = model.types.get(i).intValue();
                    j++;
                }
            }
            if (j != size) {
                values = ArrayUtils.subArray(values, j);
                types = ArrayUtils.subArray(types, j);
            }
            StringBuilder sql = new StringBuilder("INSERT INTO");
            sql.append(" ").append(model.table);
            sql.append(" (").append(sql_fields.substring(1)).append(")");
            sql.append(" VALUES (").append(sql_values.substring(1)).append(")");
            return save(sql.toString(), values, types, model.pkName, jt) > 0;
        }
    }

    /**
     * 批量更新
     *
     * @param sql
     * @param valueList
     * @param types     java.sql.Types
     * @param jt
     * @return
     */
    public static int batchUpdate(String sql, final List<Object[]> valueList, final int[] types, JdbcTemplate jt) {
        BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int index) throws SQLException {
                Object[] values = valueList.get(index);
                PreparedStatementCreatorImpl.setValues(ps, values, types);
            }

            public int getBatchSize() {
                return valueList.size();
            }
        };
        String logstr = null;
        if (log.isDebugEnabled()) {
            logstr = "batchUpdate: sql=" + sql + "; parameters=" + arrayListToString(valueList);
            log.debug(logstr);
        }
        int[] rows = null;
        if (types == null) {
            rows = jt.batchUpdate(sql, valueList);
        } else {
            rows = jt.batchUpdate(sql, setter);
        }
        int total = 0;
        for (int r : rows) {
            if (r == -2) {
                total += 1;
            } else if (r > 0) {
                total += r;
            }
        }
        if (log.isInfoEnabled()) {
            if (logstr == null) {
                logstr = "batchUpdate: sql=" + sql + "; parameters=" + arrayListToString(valueList);
            }
            log.info(logstr + "; rows=" + total);
        }
        return total;
    }

    /**
     * 将结果集转成实体对象
     *
     * @param rs
     * @param clazz
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    protected static <T> T convert2Object(ResultSet rs, Class<T> clazz) throws SQLException, DataAccessException {
        ResultSetMetaData meta = rs.getMetaData();
        int colCount = meta.getColumnCount();
        // columns[0]-数据集中的原始名, columns[1]-对应子类中的属性名
        List<String[]> columns = new ArrayList<>();
        for (int i = 1; i <= colCount; i++) {
            columns.add(new String[]{meta.getColumnName(i), meta.getColumnName(i)});
        }
        return (T) convert2Object(rs, clazz, columns);
    }

    /**
     * 将结果集转成实体对象
     *
     * @param rs
     * @param clazz
     * @param columns
     * @return
     * @throws SQLException
     * @throws DataAccessException
     */
    private static Object convert2Object(ResultSet rs, Class<?> clazz, List<String[]> columns) throws SQLException, DataAccessException {
        Object object = null;
        Class<?> fieldType;
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        try {
            object = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
            return null;
        }
        Method method;
        String column, subObjName;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        String[] columnName;
        for (BeanField field : fields) {
            method = field.getSetterMethod();
            if (method == null) {
                continue;
            }
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            column = getColumnName(annotation, field.field);
            if (column == null) {
                continue;
            }

            List<String[]> subColumns = null;//子对象字段，String[]{数据库字段名,实体属性名}
            for (int i = 0; i < columns.size(); i++) {
                columnName = columns.get(i);
                if (column.equalsIgnoreCase(columnName[1]) || field.getName().equalsIgnoreCase(columnName[1])) {
                    fieldType = method.getParameterTypes()[0];
                    if (annotation != null && annotation.isJson()) {
                        String json = rs.getString(columnName[0]);
                        try {
                            if (json != null && !(json = json.trim()).isEmpty()) {
                                if (fieldType.isAssignableFrom(List.class)) {
                                    Class<?> genericClazz = ReflectUtils.getGenericType(field.field);
                                    method.invoke(object, JSONArray.parseArray(json, genericClazz));
                                } else {
                                    method.invoke(object, JSONObject.parseObject(json, fieldType));
                                }
                            }
                        } catch (JSONException | IllegalAccessException | InvocationTargetException e) {
                            log.error("field=" + field.getName() + ", method=" + method.getName() + ", column=" + columnName[0] + ", json=" + json + ", " +
                                    "error=" + e
                                    .toString(), e);
                        }
                    } else {
                        invoke(rs, object, method, fieldType, columnName[0], annotation);
                    }
                    columns.remove(i);//移除已匹配过的字段，减少下次循环次数
                    break;
                }
                int ii = columnName[1].indexOf(".");
                if (ii > 0) {// 子对象字段
                    subObjName = columnName[1].substring(0, ii);
                    if (column.equalsIgnoreCase(subObjName) || field.getName().equalsIgnoreCase(subObjName)) {
                        if (subColumns == null) {
                            subColumns = new ArrayList<>();
                        }
                        subColumns.add(new String[]{columnName[0], columnName[1].substring(ii + 1)});
                        columns.remove(i);//移除已匹配过的字段，减少下次循环次数
                        i--;
                    }
                }
            }
            if (subColumns != null) {
                Object val = convert2Object(rs, method.getParameterTypes()[0], subColumns);
                if (val != null) {
                    try {
                        method.invoke(object, val);
                    } catch (Exception e) {
                        throw new DbException(e);
                    }
                }
            }
        }

        return object;
    }

    protected static Object convert2Entity(ResultSet rs, Class<?> clazz, List<EntityField> fields) throws SQLException, DataAccessException {
        Object object = null;
        Class<?> fieldType;
        try {
            object = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
            return null;
        }
        String columnName = null;
        Object obj = null;
        Method method;
        for (EntityField entityfield : fields) {
            method = entityfield.setter;
            fieldType = method.getParameterTypes()[0];
            columnName = entityfield.columnName;
            if (entityfield.type == FieldType.ENTITY) {
                obj = convert2Entity(rs, fieldType, entityfield.entityFields);
                if (obj != null) {
                    try {
                        method.invoke(object, obj);
                    } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                        log.error("method=" + method + ", fieldType=" + fieldType + ", error=" + e.toString(), e);
                    }
                }
            } else if (entityfield.type == FieldType.JSON_ARRAY) {
                String json = rs.getString(entityfield.columnName);
                if (json != null && !(json = json.trim()).isEmpty()) {
                    try {
                        method.invoke(object, JSONArray.parseArray(json, entityfield.genericClazz));
                    } catch (JSONException e) {
                        log.error("method=" + method + ", column=" + columnName + ", json=" + json + ", error=" + e.toString(), e);
                    } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                        log.error("method=" + method + ", column=" + columnName + ", error=" + e.toString(), e);
                    }
                }
            } else if (entityfield.type == FieldType.JSON_OBJECT) {
                String json = rs.getString(entityfield.columnName);
                try {
                    method.invoke(object, JSONObject.parseObject(json, fieldType));
                } catch (JSONException e) {
                    log.error("method=" + method + ", column=" + columnName + ", json=" + json + ", error=" + e.toString(), e);
                } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                    log.error("method=" + method + ", column=" + columnName + ", error=" + e.toString(), e);
                }
            } else {
                invoke(rs, object, method, fieldType, columnName, entityfield.annotation);
            }
        }

        return object;
    }

    protected static Map<String, Object> convert2Map(ResultSet rs, List<Object[]> columns) throws SQLException {
        Map<String, Object> m = new HashMap<>();
        for (Object[] col : columns) {
            String fieldName = (String) col[0];
            int type = (int) col[1];
            Object fieldValue = null;
            if (type == Types.VARCHAR) {
                fieldValue = rs.getString(fieldName);
            } else if (type == Types.INTEGER) {
                fieldValue = rs.getInt(fieldName);
            } else if (type == Types.BIGINT) {
                fieldValue = rs.getLong(fieldName);
            } else if (type == Types.DATE) {
                fieldValue = formatDate(rs.getTimestamp(fieldName));
            } else if (type == Types.BLOB) {
                fieldValue = rs.getBytes(fieldName);
            } else if (type == Types.CLOB) {
                fieldValue = rs.getString(fieldName);
            } else if (type == Types.TIMESTAMP) {
                Timestamp tt = rs.getTimestamp(fieldName);
                if (tt != null) {
                    fieldValue = tt.getTime();
                }
            } else if (type == Types.SMALLINT) {
                fieldValue = rs.getShort(fieldName);
            } else if (type == Types.LONGVARCHAR) {
                String value = rs.getString(fieldName);
                if (value != null && !value.isEmpty()) {
                    try {
                        if (value.matches("\\{.*\\}")) {
                            fieldValue = JSONObject.parseObject(value, Map.class);
                        } else if (value.matches("\\[.*\\]")) {
                            fieldValue = JSONArray.parseArray(value, Map.class);
                        } else {
                            fieldValue = value;
                        }
                    } catch (Exception e) {
                        log.warn(e.getMessage(), e);
                        fieldValue = value;
                    }
                }
            } else {
                fieldValue = rs.getObject(fieldName);
            }
            m.put(fieldName, fieldValue);
        }
        return m;
    }

    /**
     * 生成实体对象模型
     *
     * @param clazz
     * @param columns
     * @return
     */
    protected static List<EntityField> buildEntityModel(Class<?> clazz, List<String[]> columns) {
        Class<?> fieldType;
        List<BeanField> fields = ReflectUtils.getBeanFields(clazz);
        Method method;
        String column, subObjName;
        com.cnsugar.common.jdbc.annotation.Field annotation = null;
        String[] columnName;
        List<EntityField> entityFields = new ArrayList<>();// 普通字段
        for (BeanField field : fields) {
            method = field.getSetterMethod();
            if (method == null) {
                continue;
            }
            annotation = field.getAnnotation(com.cnsugar.common.jdbc.annotation.Field.class);
            column = getColumnName(annotation, field.field);
            if (column == null) {
                continue;
            }

            List<String[]> subColumns = null;//子对象字段，String[]{数据库字段名,实体属性名}
            for (int i = 0; i < columns.size(); i++) {
                columnName = columns.get(i);
                if (column.equalsIgnoreCase(columnName[1]) || field.getName().equalsIgnoreCase(columnName[1])) {
                    fieldType = method.getParameterTypes()[0];
                    if (annotation != null && annotation.isJson()) {
                        if (fieldType.isAssignableFrom(List.class)) {
                            EntityField entityField = new EntityField(annotation, method, columnName[0], FieldType.JSON_ARRAY);
                            entityField.genericClazz = ReflectUtils.getGenericType(field.field);
                            entityFields.add(entityField);
                        } else {
                            entityFields.add(new EntityField(annotation, method, columnName[0], FieldType.JSON_OBJECT));
                        }
                    } else {
                        entityFields.add(new EntityField(annotation, method, columnName[0], FieldType.JAVA));
                    }
                    columns.remove(i);//移除已匹配过的字段，减少下次循环次数
                    break;
                }
                int ii = columnName[1].indexOf(".");
                if (ii > 0) {// 子对象字段
                    subObjName = columnName[1].substring(0, ii);
                    if (column.equalsIgnoreCase(subObjName) || field.getName().equalsIgnoreCase(subObjName)) {
                        if (subColumns == null) {
                            subColumns = new ArrayList<>();
                        }
                        subColumns.add(new String[]{columnName[0], columnName[1].substring(ii + 1)});
                        columns.remove(i);//移除已匹配过的字段，减少下次循环次数
                        i--;
                    }
                }
            }
            if (subColumns != null) {
                List<EntityField> lst = buildEntityModel(method.getParameterTypes()[0], subColumns);
                if (lst != null) {
                    entityFields.add(new EntityField(lst, method, FieldType.ENTITY));
                }
            }
        }

        if (entityFields.isEmpty()) {
            return null;
        }
        return entityFields;
    }

    /**
     * 动态调用setter方法设置值
     *
     * @param rs
     * @param object
     * @param method
     * @param fieldType
     * @param columnName
     * @param annotation
     */
    private static final void invoke(ResultSet rs, Object object, Method method, Class fieldType, String columnName, com.cnsugar.common.jdbc.annotation.Field
            annotation) throws SQLException {
        try {
            if (fieldType == String.class) {
                if (annotation != null && annotation.format() != null && !annotation.format().isEmpty()) {
                    method.invoke(object, formatDate(rs.getString(columnName), annotation.format()));
                } else {
                    method.invoke(object, rs.getString(columnName));
                }
            } else if (fieldType == Integer.class || fieldType == int.class) {
                method.invoke(object, rs.getInt(columnName));
            } else if (fieldType == Long.class || fieldType == long.class) {
                method.invoke(object, rs.getLong(columnName));
            } else if (fieldType == Float.class || fieldType == float.class) {
                if (annotation != null && annotation.scale() > 0) {
                    method.invoke(object, formatFloat(rs.getFloat(columnName), annotation.scale()));
                } else {
                    method.invoke(object, rs.getFloat(columnName));
                }
            } else if (fieldType == Double.class || fieldType == double.class) {
                if (annotation != null && annotation.scale() > 0) {
                    method.invoke(object, formatDouble(rs.getDouble(columnName), annotation.scale()));
                } else {
                    method.invoke(object, rs.getDouble(columnName));
                }
            } else if (fieldType == java.util.Date.class || fieldType == Timestamp.class) {
                method.invoke(object, rs.getTimestamp(columnName));
            } else if (fieldType == Date.class) {
                method.invoke(object, rs.getDate(columnName));
            } else if (fieldType == byte[].class) {
                method.invoke(object, rs.getBytes(columnName));
            } else if (fieldType == Short.class || fieldType == short.class) {
                method.invoke(object, rs.getShort(columnName));
            }
        } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
            log.error("method=" + method.getName() + ", column=" + columnName + ", error=" + e.toString(), e);
        }
    }

    /**
     * 获取对应的数据库字段名
     *
     * @param annotation
     * @param field
     * @return
     */
    private static final String getColumnName(com.cnsugar.common.jdbc.annotation.Field annotation, Field field) {
        if (annotation == null) {
            return CamelCaseUtils.toUnderlineName(field.getName());
        }
        if (!annotation.isColumn()) {
            return null;
        }
        if (annotation.value() != null && !annotation.value().isEmpty()) {
            return annotation.value();
        } else {
            return CamelCaseUtils.toUnderlineName(field.getName());
        }
    }

    /**
     * Object[] 转成 String
     *
     * @param objs 对象数组
     * @return
     */
    protected static String arrayToString(Object[] objs) {
        if (objs == null) {
            return "[]";
        }
        StringBuffer buf = new StringBuffer();
        buf.append("[");
        for (int j = 0; j < objs.length; j++) {
            if (j > 0) {
                buf.append(", ");
            }
            buf.append(String.valueOf(objs[j]));
        }
        buf.append("]");
        return buf.toString();
    }

    protected static String arrayListToString(List<Object[]> list) {
        if (list == null || list.isEmpty()) {
            return "[]";
        }
        StringBuffer buf = new StringBuffer();
        for (Object[] obj : list) {
            buf.append(", ").append(arrayToString(obj));
        }
        return "[" + buf.substring(2) + "]";
    }

    protected static double formatDouble(double d, int scale) {
        if (d == 0) {
            return d;
        }
        try {
            return new BigDecimal(d).setScale(scale, BigDecimal.ROUND_HALF_UP).doubleValue();
        } catch (Exception e) {
            e.getMessage();
            return d;
        }
    }

    protected static float formatFloat(float f, int scale) {
        if (f == 0) {
            return f;
        }
        try {
            return new BigDecimal(f).setScale(scale, BigDecimal.ROUND_HALF_UP).floatValue();
        } catch (Exception e) {
            e.getMessage();
            return f;
        }
    }

    protected static String formatDate(String str, String format) {
        if (str == null || str.isEmpty()) {
            return str;
        }
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        try {
            return sdf.format(sdf.parse(str));
        } catch (Exception e) {
            log.debug("Date format error: value=" + str + ", error=" + e.toString());
            return str;
        }
    }

    protected static String formatDate(Timestamp date) {
        return formatDateTime(date, "yyyy-MM-dd HH:mm:ss");
    }

    protected static String formatDateTime(java.util.Date date, String parrent) {
        if (date == null) {
            return null;
        }
        try {
            SimpleDateFormat sdf = new SimpleDateFormat(parrent);
            return sdf.format(date);
        } catch (Exception e) {
            e.printStackTrace();
            return date.toString();
        }
    }

    /**
     * 结果集处理
     *
     * @param <T>
     */
    protected static class ResultSetExtractorImpl<T> implements ResultSetExtractor<List<T>> {
        private Class<T> clazz;

        public ResultSetExtractorImpl(Class<T> clazz) {
            this.clazz = clazz;
        }

        @SuppressWarnings({"unchecked"})
        public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (!rs.next()) {
                return null;
            }
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            // columns[0]-数据集中的原始名, columns[1]-对应子类中的属性名
            List<String[]> columns = new ArrayList<>();
            for (int i = 1; i <= colCount; i++) {
                columns.add(new String[]{meta.getColumnName(i), meta.getColumnName(i)});
            }
            List<EntityField> fields = buildEntityModel(clazz, columns);
            if (fields == null) {
                return null;
            }
            List<T> list = new ArrayList<T>();
            do {
                list.add((T) convert2Entity(rs, clazz, fields));
            } while (rs.next());
            return list;
        }
    }

    protected static class EntityField {
        public com.cnsugar.common.jdbc.annotation.Field annotation;// annotation类
        public String columnName;
        public FieldType type; // 字段类型
        public Class<?> genericClazz;// json数组元素对象
        public List<EntityField> entityFields;
        public Method setter;

        public EntityField(com.cnsugar.common.jdbc.annotation.Field annotation, Method setter, String columnName, FieldType type) {
            this.annotation = annotation;
            this.setter = setter;
            this.columnName = columnName;
            this.type = type;
        }

        public EntityField(List<EntityField> entityFields, Method setter, FieldType type) {
            this.entityFields = entityFields;
            this.setter = setter;
            this.type = type;
        }
    }
}
